

*Written by Luo Mi, date unkonwn.
*Heavily audited by Jason Premo, 6/13/16-8/1/16
*
*Code generates the data for the balance sheet in the second Townsend paper from the raw HRS data.
*Codebook: randhrsN.pdf (search for the variable names to get a sense of what's going on here).
*Also note: There are a couple of variables which are generated but, to the best of my knowledge,
*never used. I leave them here just in case.
*
* Outputs to: Townsend/results/balance_sheet.xlsx
** Filepath: /shared/sdsjxs/Townsend/scripts/hrs_clean.do

clear all
set more off
set maxvar 20000
pause on
cap log close


cap program drop output_all
program define output_all
	args remove sheetname income
	
	cap drop a* f* h* i* p* r* s* hhid
	cap drop i* h* m*
	qui describe, varlist
	egen double all = rowtotal(`r(varlist)')
	replace all = all `remove'

	if "`income'" == "yes" {
		gen all_censored = all if all <= 200000
	}

	
	qui describe, varlist
	local meanlist = ""
	local medianlist = ""

	*Not really using the medians, except for the totals
	foreach variable in `r(varlist)' {
		if "`variable'" == "weight" {
			continue
			}
		local tempname1 = "mean_`variable' = `variable' "
		local tempname2 = "med_`variable' = `variable' "
		local meanlist = "`meanlist'"+"`tempname1'"
		local medianlist = "`medianlist'"+"`tempname2'"
		}
	
	preserve
	
	collapse (mean) `meanlist' (p50) med_all=all [aw = weight]
	xpose, varname clear
	rename _varname varname
	order varname
	replace v1 = round(v1)

	export excel using "$out_path/balance_sheet.xlsx", sheet("hrs_`sheetname'", replace)
end




*log using "/shared/sdsjxs/DavidSmallProjects/Surveys_Townsend/logfiles/hrs_means.log", replace

*AUDIT: All globals changed to locals. Paths updated (most deleted). Some names changed, for my sanity.
local in_path "/shared/sdsjxs/Townsend/rawdata/hrs"
global out_path "/shared/sdsjxs/Townsend/results"

******************************** PSID 2013 *************************************

use "`in_path'/rndhrs_n.dta", clear

*JASON: Using household weights, so this checks out.
gen weight  	= r11wthh
*HH weight as analytic weight, integers

tempfile start
save `start', replace

********************************* 1. Assets ************************************


*Off limit variable name beginning letters for final vars: a f h i p r s
*(They are dropped in the janky program above.)

*AUDIT: Non-primary residence
gen home_invest = h11arles

*AUDIT: Vehicles
gen vehicle 	= h11atran

*AUDIT: Business
gen business 	= h11absns

*AUDIT: Net IRA and Keogh
gen xira 	= h11aira

*AUDIT: Net value of stocks, mutual funds, investment trusts
gen xstock 	= h11astck

*AUDIT: Checking, savings, and money market accounts
gen checking 	= h11achck

*AUDIT: Cds, government bonds, t-bills
gen cd 		= h11acd

*AUDIT: All bonds (overlaps with above category)
gen bond 	= h11abond

*AUDIT: Anything not included in any other category that one could reasonably call an asset.
*i.e. "Morty owes me fifty bucks."
gen othrsave	= h11aothr

*AUDIT: Gross value of primary residence
gen xhouse1	= h11ahous

*AUDIT: Gross value of second residence
gen house2 	= h11ahoub

egen xother_housing = rowtotal(house2 home_invest)
drop house2 home_invest

output_all " - weight" "assets"

********************************** 2. Debts ************************************

use `start', clear

*Off limit variable name beginning letters for final vars: a f h i p r s

*AUDIT: Gross value of all other debt.
gen othrdebt	= h11adebt

*AUDIT: Gross value of mortgage
gen mort1 	= h11amort

*AUDIT: Gross value of other home loans (primary residence)
gen xhomeln1 	= h11ahmln

*AUDIT: Value of secondary residence mortgage
gen mort2 	= h11amrtb

output_all " - weight" "liabilities"

********************************* 3. Income ************************************

use `start', clear

* According to the documentation, total income consists of
* RwIEARN, SwIEARN, HwICAP, RwIPENA, SwIPENA, RwISSDI,
* SwISSDI, RwISRET, SwISRET, RwIUNWC, SwIUNWC, RwIGXFR, SwIGXFR, and HwIOTHR
* (w is wave, 11 for us). There is also a total income variable H11ITOT 

*Off limit variable name beginning letters for final vars: a f h i p r s

*AUDIT: Individual earnings (individual and spouse)
gen labinc1 	= r11iearn
gen labinc2 	= s11iearn
egen labinc 	= rowtotal(labinc1 labinc2)
drop labinc1 labinc2

*AUDIT: Capital income
gen capinc 	= h11icap

*AUDIT: Pensions and Annuities
gen pensann1 	= r11ipena
gen pensann2 	= s11ipena
egen pensann 	= rowtotal(pensann1 pensann2)
drop pensann1 pensann2

*AUDIT: Individual and Spousal Disability/SSI income
gen ssdi1 	= r11issdi
gen ssdi2 	= s11issdi
egen ssdi 	= rowtotal(ssdi1 ssdi2)
drop ssdi1 ssdi2

*AUDIT: Individual and Spousal social security income
gen ssret1 	= r11isret
gen ssret2 	= s11isret
egen ssret 	= rowtotal(ssret1 ssret2)
drop ssret?

*AUDIT: Individual and Spousal Worker's Comp
gen unwc1 	= r11iunwc
gen unwc2 	= s11iunwc
egen unwc 	= rowtotal(unwc1 unwc2)

*AUDIT: Individual and Spousal "Other Government Transfers"
*Sum of VA Benefits, Food Stamps, and Welfare
gen govtrans1	= r11igxfr
gen govtrans2 	= s11igxfr
egen govtrans 	= rowtotal(govtrans1 govtrans2 ssdi unwc ssret)
drop ssdi govtrans? unwc?

*AUDIT: Other Household Income
egen othrinc 	= rowtotal(h11iothr pensann)
drop pensann

*AUDIT: Total Household Income (individual+spouse)
gen totinc_reported 	= h11itot


output_all " - weight -totinc_reported" "income" yes

******************************** 4. Expenses ***********************************

*RAND CAMS: Documentation is RAND_CAMS_Data_Documentation_D2.pdf
use "`in_path'/randcams_d2.dta", clear
gen weight 		= h11cwgthh

*AUDIT: Total Household Spending
gen totexp_reported 	= h11ctots

*AUDIT: Transportation Spending
gen transexp 	= h11ctranss

*AUDIT: Total Housing Spending
gen xhouseexp	= h11chouss

*AUDIT: Car Purchases and Payments (NOT MUTUALLY EXCLUSIVE WITH TRANSPORTATION SPENDING)
*gen carexp 	= h11cautoall

*AUDIT: Mortgage payments and interest (NOT MUTUALLY EXCLUSIVE WITH HOUSEHOLD SPENDING)
*gen xmortexp	= h11cmort


output_all " - weight - totexp_reported" "expenditure"


cap log close

